package com.chen.dao.impl;

import com.chen.dao.TrolleyDao;
import com.chen.entity.Comment;
import com.chen.entity.Trolley;
import com.chen.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class TrolleyDaoImpl implements TrolleyDao {

    Connection conn = DBUtil.getConnection();
    QueryRunner qr = new QueryRunner();

    @Override
    public int save(Trolley trolley) {
        String sql = " INSERT INTO  tb_trolley(trolley_id,user_id,goods_id,delivery_id, " +
                " trolley_num,trolley_status) VALUES (?,?,?,?,?,?) ";
        Object[] paras = {trolley.getTrolley_id(),trolley.getUser_id(),trolley.getGoods_id(),
                        trolley.getDelivery_id(),trolley.getTrolley_num(),trolley.getTrolley_status()};
        Comment comment = null;
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Trolley trolley) {
        String sql = "UPDATE  tb_trolley SET  user_id=?,goods_id=?,delivery_id=?, " +
                " trolley_num=?,trolley_status=? WHERE trolley_id=?  ";
        Object[] paras = {trolley.getUser_id(),trolley.getGoods_id(),trolley.getDelivery_id(),
                trolley.getTrolley_num(),trolley.getTrolley_status(),trolley.getTrolley_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Object id) {
        String sql = " DELETE FROM tb_trolley WHERE trolley_id  = ?  ";
        Object[] paras = {id};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Trolley findById(Object id) {
        String sql = "SELECT * FROM tb_trolley where trolley_id  = ?";
        Object[] paras = {id};
        try {
            Trolley trolley = qr.query(conn, sql, paras, new BeanHandler<Trolley>(Trolley.class));
            return trolley;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Trolley> findAll() {
        String sql = "SELECT * FROM tb_trolley";
        Object[] paras = {};
        try {
            List<Trolley> trolleyList = qr.query(conn, sql, paras, new BeanListHandler<Trolley>(Trolley.class));
            return trolleyList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Trolley> findSearch(String keyword) {
        String sql = "SELECT * FROM tb_trolley where trolley_id like concat('%',concat(?,'%'))";
        Object[] paras = {keyword};
        try {
            List<Trolley> trolleyList = qr.query(conn, sql, paras, new BeanListHandler<Trolley>(Trolley.class));
            return trolleyList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Trolley> findPage(int page, int pagesize) {
        String sql = "SELECT * FROM tb_trolley LIMIT ?,?";
        Object[] paras = {(page-1)*pagesize,pagesize};
        try {
            List<Trolley> commentList = qr.query(conn, sql, paras, new BeanListHandler<Trolley>(Trolley.class));
            return commentList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long count() {
        String sql = "select count(*) from tb_trolley";
        Object[] paras = {};
        try {
            long nums = qr.query(conn, sql, paras, new ScalarHandler<Long>());
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
